﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Web;
using System.IO;
using System.Threading;

namespace DK.ExcelExport
{
    /// <summary>
    /// Class to export dataset tables in to excel using open xml method
    /// </summary>
    public sealed class Export2Excel
    {
        #region Member Variables and Properties
        /// <summary>
        /// Private Variable for exporting file in to response object
        /// </summary>
        private static bool _isExport2Response;

        /// <summary>
        /// If file to be exported through response or needs to be saved at some location
        /// </summary>
        public static bool IsExport2Response
        {
            get { return Export2Excel._isExport2Response; }
            set { Export2Excel._isExport2Response = value; }
        }
        /// <summary>
        /// Private variable for exported file
        /// </summary>
        private static string _path2Export;
        /// <summary>
        /// If file needs to be saved at any location then set the value of this parameter to that location else leave it blank
        /// </summary>
        public static string Path2Export
        {
            get { return Export2Excel._path2Export; }
            set { Export2Excel._path2Export = value; }
        }

        private static HttpResponse _resp;
        /// <summary>
        /// Response object in which file needs to be exported
        /// </summary>
        public static HttpResponse ResponseObject { set { _resp = value; } }

        /// <summary>
        /// private varible containing the data to be exported
        /// </summary>
        private static DataSet _dataset4Export;
        /// <summary>
        /// DataSet containing data to be exported
        /// </summary>
        public static DataSet Dataset4Export { set { _dataset4Export = value; } }

        /// <summary>
        /// private variable to get file names
        /// </summary>
        private static string[] _sheetaNames;
        /// <summary>
        /// List of the sheet names
        /// </summary>
        public static string[] SheetNames { set { _sheetaNames = new string[] { }; _sheetaNames = value; } }
        private static string _fileName;
        /// <summary>
        /// Exported File name
        /// </summary>
        public static string FileName { 
            set {
                if (value.LastIndexOf(".") > 0 && (value.Substring(value.LastIndexOf(".")).ToLower() == "xls" || value.Substring(value.LastIndexOf(".")).ToLower() == "xlsx"))
                    _fileName = value;
                else
                    _fileName = value + ".xls";
                } 
        }
        private static string _errorMessage;
        /// <summary>
        /// Error message returned to user if any
        /// </summary>
        public static string ErrorMessage { get { return _errorMessage; } }

        #endregion

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        private static bool MandatoryCheck()
        {
            if (_dataset4Export == null || _dataset4Export.Tables.Count == 0)
            {
                _errorMessage = "Either Dataset is not set or it doesnt contains any data";
                return false;
            }
            else if (_isExport2Response == true && _resp == null)
            {
                _errorMessage = "Export to response is set to true and its object is not supplied";
                return false;
            }
            else if (_isExport2Response == false && string.IsNullOrEmpty(_path2Export) == true)
            {
                _errorMessage = "Export to response is set to false and no file path is provided";
                return false;
            }

            return true;
        }

        /// <summary>
        /// 
        /// </summary>
        public static void Export()
        {
            if (MandatoryCheck())
            {
                StringBuilder sbStartContent = new StringBuilder();
                sbStartContent.Append("<?xml version='1.0'?>");
                sbStartContent.Append("<ss:Workbook xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet'>");
                sbStartContent.Append("<ss:Styles>");
                sbStartContent.Append("<ss:Style ss:ID='1'>");
                sbStartContent.Append("<ss:Font ss:Bold='1'/>");
                sbStartContent.Append("</ss:Style>");
                sbStartContent.Append("</ss:Styles>");
                StringBuilder sbSheetContent = new StringBuilder();
                foreach (DataTable dtTable in _dataset4Export.Tables)
                {
                    CreateWorksheet(dtTable, sbSheetContent);
                }
                StringBuilder sbEndContents = new StringBuilder();
                sbEndContents.Append("</ss:Workbook>");
                StringBuilder sbFileContent = new StringBuilder();
                sbFileContent.Append(sbStartContent.ToString());
                sbFileContent.Append(sbSheetContent.ToString());
                sbFileContent.Append(sbEndContents.ToString());
                ExportExcelFile(sbFileContent);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sbFileContent"></param>
        private static void ExportExcelFile(StringBuilder sbFileContent)
        {
            try
            {
                if (_isExport2Response)
                {

                   // _resp.ContentType = "";
                    _resp.Clear();     
                    _resp.Buffer = true;     
                    _resp.AppendHeader("Content-Disposition","attachment;filename="+_fileName);           
                    _resp.ContentEncoding = System.Text.Encoding.ASCII;
                    _resp.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                   
                    //StreamWriter swr = new StreamWriter(
                   MemoryStream mstream = new MemoryStream(ASCIIEncoding.ASCII.GetBytes(sbFileContent.ToString()));
                    mstream.WriteTo(_resp.OutputStream);
                    mstream.Close();
                    mstream.Dispose();                   
                    _errorMessage = "Data Exported  Successfully";
                }
                else
                {
                    StreamWriter sw = null;
                    sw = new StreamWriter(_path2Export + "\\" + _fileName);
                    sw.Write(sbFileContent.ToString());
                    sw.Flush();
                    sw.Close();
                _errorMessage="Data Exported to File Successfully. Open it from " +_path2Export + "\\" +_fileName;
                }
                
            }
            catch (Exception ex)
            {
                _errorMessage = ex.Message;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dtTable"></param>
        /// <param name="sbSheetContent"></param>
        private static void CreateWorksheet(DataTable dtTable, StringBuilder sbSheetContent)
        {
            StringBuilder sbData = new StringBuilder();
            sbData.Append("<ss:Worksheet ss:Name='" + dtTable.TableName + "'><ss:Table>");

            foreach (DataColumn col in dtTable.Columns)
            {
                sbData.Append("<ss:Column ss:Width='"+(col.MaxLength+100).ToString()+"'/>");
            }
                sbData.Append("<ss:Row ss:StyleID='1'>");
                foreach (DataColumn dc in dtTable.Columns)
                {
                  sbData.Append("<ss:Cell>");
                   sbData.Append("<ss:Data ss:Type='String'>"+dc.ColumnName +"</ss:Data>");
                   sbData.Append("</ss:Cell>");
                }
                sbData.Append("</ss:Row>");
                for (int i = 0; i < dtTable.Rows.Count; i++)
                {
                    sbData.Append("<ss:Row>");
                    for (int j = 0; j < dtTable.Columns.Count; j++)
                    {
                        sbData.Append("<ss:Cell>");
                        sbData.Append("<ss:Data ss:Type='String'>" + dtTable.Rows[i][j].ToString() + "</ss:Data>");
                        sbData.Append("</ss:Cell>");  
                    }
                    sbData.Append("</ss:Row>");
                }
                sbData.Append("</ss:Table>");
                sbData.Append("</ss:Worksheet>");
                sbSheetContent.Append(sbData.ToString());
        }
    }
}
